<?php declare(strict_types=1);

namespace App\Services\Order;

use App\Extensions\oss\Oss;
use App\Jobs\MerchantOrderQueryExportJob;
use App\Models\Bi\BillLog;
use App\Models\Bi\BiOrder;
use App\Models\Bi\BiRefundOrder;
use App\Models\MerchantStore;
use App\Models\Order;
use App\Models\OrderGoodsDetail;
use App\Models\StoreAllPayWayList;
use App\Models\StoreGoods;
use App\Repository\OrderExportRepo;
use App\Services\PrintSqlService;
use App\Util\ExeclUtil;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;
use Illuminate\Support\Facades\Schema;
use ZipArchive;
class MerchantService
{


    const TmpTableName = "bi_orders";

    const TmpLogTableName = "bill_logs";


    public function setWhere($query, array $where, string $table = "")
    {
        if (!empty($table)) {
            $table .= ".";
        }

        if (!empty($where["merchant_all_store_ids"])) {
            $query->whereIn($table . "store_id", $where["merchant_all_store_ids"]);
        }

        if (!empty($where["store_id"])) {
            if (is_array($where["store_id"])) {
                $query->whereIn($table . "store_id", $where["store_id"]);
            } else {
                $query->where($table . "store_id", $where["store_id"]);
            }
        }
        if (!empty($where["merchant_id"])) {
            $query->where($table . "merchant_id", $where["merchant_id"]);
        }

        if (!empty($where["company"])) {
            $query->where($table . "company", $where["company"]);
        }


        if (!empty($where["start_date"])) {
            $query->where($table . "created_at", ">=", $where["start_date"]);
        }

        if (!empty($where["end_date"])) {
            $query->where($table . "created_at", "<=", $where["end_date"]);
        }

        if (!empty($where["trade_no"])) {
            $query->where($table . "trade_no", "=", $where["trade_no"]);
        }

        if (!empty($where["out_trade_no"])) {
            $query->where($table . "out_trade_no", "=", $where["out_trade_no"]);
        }

        if (!empty($where["device_id"])) {
            $query->where($table . "device_id", "=", $where["device_id"]);
        }
        if (!empty($where["ways_source"])) {
            $query->where($table . "ways_source", "=", $where["ways_source"]);
        }
        if (!empty($where["ways_type"])) {
            $query->where($table . "ways_type", "=", $where["ways_type"]);
        }

        if (isset($where["discount"]) && $where["discount"] == 1) {
            $query->where($table . "discount_amount", ">", 0);
        }

        if (!empty($where["auth_code"])) {
            $query->where($table . "auth_code", "=", $where["auth_code"]);
        }

        if (!empty($where["qwx_no"])) {
            $query->where($table . "qwx_no", "=", $where["qwx_no"]);
        }

        if (!empty($where["pay_status"])) {
            $query->where($table . "pay_status", "=", $where["pay_status"]);
        }

    }


    public function getTableName(string $time, bool $as = true, string $prefix = ""): string
    {
        $tableName = $prefix . "orders_" . date("Ym", strtotime($time));
        if (!$as) {
            return $tableName;
        }
        return $tableName . " as " . $prefix . self::TmpTableName;
    }


    public function setBillWhere($query, array $where, string $table = "")
    {
        if (!empty($table)) {
            $table .= ".";
        }

        if (!empty($where["merchant_all_store_ids"])) {
            $query->whereIn($table . "store_id", $where["merchant_all_store_ids"]);
        }

        if (!empty($where["store_id"])) {
            if (is_array($where["store_id"])) {
                $query->whereIn($table . "store_id", $where["store_id"]);
            } else {
                $query->where($table . "store_id", $where["store_id"]);
            }
        }

        if (!empty($where["company"])) {
            $query->where($table . "company", $where["company"]);
        }

        if (!empty($where["start_date"])) {
            $query->where($table . "day", ">=", $where["start_date"]);
        }

        if (!empty($where["end_date"])) {
            $query->where($table . "day", "<=", $where["end_date"]);
        }

        if (!empty($where["ways_type"])) {
            $query->where($table . "ways_type", "=", $where["ways_type"]);
        }

    }

    public function getLogTableName(string $time, bool $as = true, string $prefix = ""): string
    {
        $tableName = $prefix . "bill_logs_" . date("Ym", strtotime($time));
        if (!$as) {
            return $tableName;
        }
        return $tableName;
    }


    public function orderQuery(array $where, bool $page = true): array
    {
        if (empty($where["start_date"]) || $where["start_date"] >= date("Y-m-d", time()) . " 00:00:00") {
            if (!empty($where["merchant_id"])) {
                $query = Order::query()->from(DB::raw("orders as `" . self::TmpTableName . "` force index(`idx_paystatus_createdat_merchantid`)"));
            } else {
                $query = Order::query()->from(DB::raw("orders as `" . self::TmpTableName . "` force index(`created_at`)"));
            }
        } else {
            if (!Schema::connection("bi")->hasTable($this->getTableName($where["start_date"], false))) {
                throw new \Exception("暂无数据1");
            }
            $query = BiOrder::query()->from($this->getTableName($where["start_date"]));
        }
        $this->setWhere($query, $where, self::TmpTableName);
        if ($page) {
            $total = $query->count();
            $query->select([self::TmpTableName . ".*", "refund_orders.created_at as refund_time"]);
            $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");
            $query->offset(($where["page"] - 1) * $where["pagesize"])
                ->limit($where["pagesize"]);
            if (isset($where["sort"]) && $where["sort"] == 1) {
                $query->orderBy(self::TmpTableName . ".total_amount", "desc");
            } else if (isset($where["sort"]) && $where["sort"] == 2) {
                $query->orderBy(self::TmpTableName . ".total_amount", "asc");
            }
            $list = $query->orderBy(self::TmpTableName . ".created_at", "desc")->get()->toArray();
        } else {
            $total = $query->count();
            $query->select([self::TmpTableName . ".*", "refund_orders.created_at as refund_time"]);
            $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");
            if (isset($where["sort"]) && $where["sort"] == 1) {
                $query->orderBy(self::TmpTableName . ".total_amount", "desc");
            } else if (isset($where["sort"]) && $where["sort"] == 2) {
                $query->orderBy(self::TmpTableName . ".total_amount", "asc");
            }
            $list = $query->orderBy(self::TmpTableName . ".created_at", "desc")->get()->toArray();
        }
        $company = StoreAllPayWayList::query()->get()->toArray();
        $company = array_combine(array_column($company, "company"), $company);

        $outTradeNoArray = array_unique(array_column($list, "out_trade_no"));
        $goodsCoupons = [];
        if (!empty($outTradeNoArray)) {
//            select out_trade_no,coupon_type,sum(coupon_amount) ,count(1) as c from order_goods_details  GROUP BY out_trade_no,coupon_type HAVING c>1
            $goodsCoupons = OrderGoodsDetail::query()
                ->select([
                    "out_trade_no", "coupon_type",
                    DB::raw("sum(coupon_amount) as coupon_amount"),
                    DB::raw("count(1) as c")
                ])
                ->whereIn("out_trade_no", $outTradeNoArray)
                ->where("status", 1)
                ->groupBy(["out_trade_no", "coupon_type"])
                ->get()->toArray();
            $goodsCoupons = array_combine(array_column($goodsCoupons, "out_trade_no"), $goodsCoupons);

            //导出数据
            $billLogs = BillLog::query()
                ->select([
                    "out_trade_no",
                    DB::raw("sum(fee_amount) as bi_fee_amount")
                ])
                ->where("out_trade_no", $outTradeNoArray)->groupBy(["out_trade_no"])->get()->toArray();
            $billLogs = array_combine(array_column($billLogs, "out_trade_no"), $billLogs);
        }

        foreach ($list as $k => $v) {
            $v["company_name"] = "";
            if (isset($company[$v["company"]])) {
                $v["company_name"] = $company[$v["company"]]["company_desc"];
            }
            $v["actual_amount"] = bcsub((string)$v["total_amount"], (string)$v["refund_amount"], 2);

            $v["refund_type"] = 0;
            $v["refund_type_name"] = "";
            if ($v["pay_status"] == 6) {
                $v["refund_type"] = 1;
                $v["refund_type_name"] = "当日退";
                if (isset($v["created_at"]) && isset($v["refund_time"])) {
                    if (date("Ymd", strtotime((string)$v["created_at"])) != date("Ymd", strtotime((string)$v["refund_time"]))) {
                        $v["refund_type"] = 2;
                        $v["refund_type_name"] = "隔日退";
                    }
                    if (date("Ym", strtotime((string)$v["created_at"])) != date("Ym", strtotime((string)$v["refund_time"]))) {
                        $v["refund_type"] = 3;
                        $v["refund_type_name"] = "隔月退";
                    }
                }
            }

            $v["coupon_type"] = 0;
            $v["coupon_amount"] = 0;
            if (!empty($goodsCoupons) && isset($goodsCoupons[$v["out_trade_no"]]) && $v["pay_status"] == 1) {
                $goodsCoupon = $goodsCoupons[$v["out_trade_no"]] ?? [];
                $v["coupon_type"] = $goodsCoupon["coupon_type"] == "COUPON" ? 1 : 2;
                $v["coupon_amount"] = $goodsCoupon["coupon_amount"] ?? 0;
            }
            if ($v["coupon_type"] == 0 && $v["discount_amount"] > 0 && $v["pay_status"] == 1) {
                $v["coupon_type"] = 1;
                $v["coupon_amount"] = $v["discount_amount"];
            }
            if ($v["fee_amount"] == 0) {
                $v["fee_amount"] = $billLogs[$v["out_trade_no"]]["bi_fee_amount"] ?? $v["fee_amount"];
            }
            $v["receipt_amount"] = bcsub((string)$v["actual_amount"], (string)$v["fee_amount"], 2);
            $list[$k] = $v;
        }
        return [
            "total" => $total,
            "list" => $list
        ];
    }

    public function customOrderQuery(array $where): array
    {
        if ($where["start_date"] >= date("Y-m-d", time()) . " 00:00:00") {
            $query = Order::query()->from("orders as " . self::TmpTableName);
        } else {
            if (!Schema::connection("bi")->hasTable($this->getTableName($where["start_date"], false))) {
                throw new \Exception("暂无数据");
            }
            $query = BiOrder::query()->from($this->getTableName($where["start_date"]));
        }
        $this->setWhere($query, $where, self::TmpTableName);
        $countQuery = clone $query;
        $total = $query->count();
        $query->select([self::TmpTableName . ".*", "refund_orders.created_at as refund_time"]);
        $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");
        $list = $query->offset(($where["page"] - 1) * $where["pagesize"])
            ->limit($where["pagesize"])
            ->orderBy(self::TmpTableName . ".created_at", "desc")->get()->toArray();

        $company = StoreAllPayWayList::query()->get()->toArray();
        $company = array_combine(array_column($company, "company"), $company);

        $outTradeNoArray = array_unique(array_column($list, "out_trade_no"));
        $goodsCoupons = [];
        if (!empty($outTradeNoArray)) {
            $goodsCoupons = OrderGoodsDetail::query()
                ->select([
                    "out_trade_no", "coupon_type",
                    DB::raw("sum(coupon_amount) as coupon_amount"),
                    DB::raw("count(1) as c")
                ])
                ->whereIn("out_trade_no", $outTradeNoArray)
                ->where("status", 1)
                ->groupBy(["out_trade_no", "coupon_type"])
                ->get()->toArray();
            $goodsCoupons = array_combine(array_column($goodsCoupons, "out_trade_no"), $goodsCoupons);
            //导出数据
            $billLogs = BillLog::query()
                ->select([
                    "out_trade_no",
                    DB::raw("sum(fee_amount) as bi_fee_amount")
                ])
                ->where("out_trade_no", $outTradeNoArray)->groupBy(["out_trade_no"])->get()->toArray();
            $billLogs = array_combine(array_column($billLogs, "out_trade_no"), $billLogs);
        }

        foreach ($list as $k => $v) {
            $v["company_name"] = "";
            if (isset($company[$v["company"]])) {
                $v["company_name"] = $company[$v["company"]]["company_desc"];
            }
            $v["actual_amount"] = bcsub((string)$v["total_amount"], (string)$v["refund_amount"], 2);
            $v["refund_type"] = 0;
            $v["refund_type_name"] = "";
            if ($v["pay_status"] == 6) {
                $v["refund_type"] = 1;
                $v["refund_type_name"] = "当日退";
                if (date("Ymd", strtotime((string)$v["created_at"])) != date("Ymd", strtotime((string)$v["refund_time"]))) {
                    $v["refund_type"] = 2;
                    $v["refund_type_name"] = "隔日退";
                }
                if (date("Ym", strtotime((string)$v["created_at"])) != date("Ym", strtotime((string)$v["refund_time"]))) {
                    $v["refund_type"] = 3;
                    $v["refund_type_name"] = "隔月退";
                }
            }

            $v["coupon_type"] = 0;
            $v["coupon_amount"] = 0;
            if (!empty($goodsCoupons) && isset($goodsCoupons[$v["out_trade_no"]]) && $v["pay_status"] == 1) {
                $goodsCoupon = $goodsCoupons[$v["out_trade_no"]] ?? [];
                $v["coupon_type"] = $goodsCoupon["coupon_type"] == "COUPON" ? 1 : 2;
                $v["coupon_amount"] = $goodsCoupon["coupon_amount"] ?? 0;
            }
            if ($v["coupon_type"] == 0 && $v["discount_amount"] > 0 && $v["pay_status"] == 1) {
                $v["coupon_type"] = 1;
                $v["coupon_amount"] = $v["discount_amount"];
            }
            if ($v["fee_amount"] == 0) {
                $v["fee_amount"] = $billLogs[$v["out_trade_no"]]["bi_fee_amount"] ?? $v["fee_amount"];
            }
            $v["receipt_amount"] = bcsub((string)$v["actual_amount"], (string)$v["fee_amount"], 2);
            $list[$k] = $v;
        }

        $countQuery->whereIn("pay_status", [1, 6]);
        $count = $countQuery->select([
            DB::raw("sum(total_amount) as total_amount"),
            DB::raw("count(1) as total_count"),
            DB::raw("count(if(pay_status=6,1,null)) as refund_count"),
            DB::raw("sum(if(pay_status=6,refund_amount,0)) as refund_amount"),
        ])->first()->toArray();
        //数据统计
        return [
            "total" => $total,
            "list" => $list,
            "count" => [
                "total_amount" => $count["total_amount"],
                "total_count" => $count["total_count"],
                "refund_count" => $count["refund_count"],
                "refund_amount" => $count["refund_amount"],
                "get_amount" => bcsub((string)$count["total_amount"], (string)$count["refund_amount"], 2)
            ]
        ];

    }

    public function orderQueryExport(array $where): array
    {
//        PrintSqlService::listen();
        ini_set('memory_limit', '512M');
        set_time_limit(0);
        if (empty($where["start_date"]) || $where["start_date"] >= date("Y-m-d", time()) . " 00:00:00") {
            $key = "";
            $prefix = "";
            $query = DB::connection();
            $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
            $query = $query->table("orders as " . self::TmpTableName);
            $isToday = true;
        } else {
            if (!Schema::connection("bi")->hasTable($this->getTableName($where["start_date"], false))) {
                throw new \Exception("暂无数据");
            }
            //历史导出记录记录下来
//            $key = md5(json_encode($where));
//            $redis = Redis::connection('default');
//            $url = $redis->get("orderQueryExportV2:" . $key);
//            if (!empty($url)) {
//                return [
//                    "url" => $url
//                ];
//            }
            $prefix = "bi_";
            $query = DB::connection("bi");
            $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
            $query = $query->table(DB::raw($this->getTableName($where["start_date"], true, $prefix) . " FORCE INDEX(created_at)"));
            $isToday = false;
        }
        $this->setWhere($query, $where, self::TmpTableName);
        $total = $query->count();
        if ($total >= 10000000) {
            throw new \Exception("导出的数据条数大于100W,请设置详细的条件");
        }

        $query->select([self::TmpTableName . ".*", "refund_orders.created_at as refund_time",
            "order_goods_details.coupon_amount as goods_coupon_amount", "order_goods_details.coupon_type as goods_coupon_type"]);
        if (!$isToday) {
            $query->addSelect([
                self::TmpLogTableName . ".bi_out_trade_no",
                self::TmpLogTableName . ".bi_fee_amount"
            ]);
        }

        $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");

        $goodsQuery = DB::table($prefix . "order_goods_details")->select([
            "out_trade_no", "coupon_type",
            DB::raw("sum(coupon_amount) as coupon_amount"),
        ])
            ->where("status", 1)
            ->groupBy(["out_trade_no", "coupon_type"]);
        $this->setWhere($goodsQuery, [
            "merchant_all_store_ids" => $where["merchant_all_store_ids"] ?? [],
            "start_date" => $where["start_date"],
            "end_date" => $where["end_date"]
        ]);
        $query->leftJoinSub($goodsQuery, $prefix . "order_goods_details", self::TmpTableName . ".out_trade_no", "=", "order_goods_details.out_trade_no");

        if (!$isToday) {
            $billQuery = DB::table($this->getLogTableName($where["start_date"], true, $prefix) . " as " . self::TmpLogTableName)->select([
                DB::raw("out_trade_no as bi_out_trade_no"),
                DB::raw("sum(fee_amount) as bi_fee_amount")
            ]);
            $this->setBillWhere($billQuery, $where, self::TmpLogTableName);
            $billQuery->groupBy(["out_trade_no"]);
            $query->leftJoinSub($billQuery, $prefix . self::TmpLogTableName, self::TmpLogTableName . ".bi_out_trade_no", "=", self::TmpTableName . ".out_trade_no");
        }
//        $sql = str_replace(array('%', '?'), array('%%', '%s'), $query->toSql());
//        $sql = vsprintf($sql, $query->getBindings());
//        dd($sql);
        if (isset($where["sort"]) && $where["sort"] == 1) {
            $query->orderBy(self::TmpTableName . ".total_amount", "desc");
        } else if (isset($where["sort"]) && $where["sort"] == 2) {
            $query->orderBy(self::TmpTableName . ".total_amount", "asc");
        }
        $orders = $query->orderBy(self::TmpTableName . ".created_at", "desc")->cursor();
        $company = StoreAllPayWayList::query()->get()->toArray();
        $company = array_combine(array_column($company, "company"), $company);

        $util = new ExeclUtil();
//        $ordersNew = $util->loadGoods($orders);
        //数据导出
        $title = [
            '门店ID', '门店名称', "设备ID", '收银员', '订单号', "原订单号", '订单金额', '退款金额', "实收金额", "实收净额", '费率', '手续费', '支付方式', '支付状态', '付款时间', '通道',
            '退款时间', "退款类型", '备注', "买家ID", "优惠券类型", "优惠券金额"
        ];
        $fileName = "订单导出" . ($where["month"] ?? date("Y-m-d")) . "_" . time() . ".csv";
        $filePath = public_path("/uploads/csv/" . date("Y/md", time()) . "/");
        //写入excel
        $util->generatorExport($filePath, $fileName, $title, $orders, function ($order) use ($company) {
            $refund_type_name = "";
            if ($order->pay_status == 6) {
                $refund_type_name = "当日退";
                if (date("Ymd", strtotime((string)$order->created_at)) != date("Ymd", strtotime((string)$order->refund_time))) {
                    $refund_type_name = "隔日退";
                }
                if (date("Ym", strtotime((string)$order->created_at)) != date("Ym", strtotime((string)$order->refund_time))) {
                    $refund_type_name = "隔月退";
                }
            }

            $coupon_type = 0;
            $coupon_type_text = "无";
            $coupon_amount = 0;

            if (!empty($order->goods_coupon_type) && $order->pay_status == 1) {
                $coupon_type = $order->goods_coupon_type == "COUPON" ? 1 : 2;
                $coupon_type_text = $order->goods_coupon_type == "COUPON" ? "预充" : "免充";
                $coupon_amount = $order->goods_coupon_amount ?? 0;
            }
            if ($coupon_type == 0 && $order->discount_amount > 0 && $order->pay_status == 1) {
                $coupon_type = 1;
                $coupon_type_text = "预充";
                $coupon_amount = $order->discount_amount;
            }
            $fee_amount = $order->fee_amount;

            if (!isset($order->bi_out_trade_no)) {
                $biLog = BillLog::query()
                    ->select([
                        "out_trade_no",
                        DB::raw("sum(fee_amount) as bi_fee_amount")
                    ])
                    ->where("out_trade_no", $order->out_trade_no)
                    ->groupBy(["out_trade_no"])->first();
                if (!empty($biLog)) {
                    $fee_amount = $biLog->bi_fee_amount;
                }
            } else {
                $fee_amount = $order->bi_fee_amount;
            }
//            $v["receipt_amount"] = bcsub((string)$v["actual_amount"], (string)$v["fee_amount"], 2);
            $actual_amount = bcsub((string)$order->total_amount, (string)$order->refund_amount, 2);
            $receipt_amount = bcsub((string)$actual_amount, (string)$fee_amount, 2);
            return [
                $order->store_id,
                $order->store_name,
                $order->device_id,
                $order->merchant_name,
                "'" . $order->out_trade_no,
                $order->trade_no,
                $order->total_amount,
                $order->refund_amount,
                $actual_amount,
                $receipt_amount,
                $order->rate . "%",
                $order->fee_amount,
                $order->ways_type_desc,
                $order->pay_status_desc,
                $order->pay_time,
                isset($company[$order->company]) ? $company[$order->company]["company_desc"] : "",
                $order->refund_time,
                $refund_type_name,
                $order->remark,
                $order->buyer_id,
                $coupon_type_text,
                $coupon_amount,
            ];
        });
        $url = $util->uploadOss($filePath . $fileName, $fileName);
        //删除文件
        if (file_exists($filePath . $fileName)) {
//            unlink($filePath . $fileName);
        }
        if (!empty($key)) {
//            $redis->set("orderQueryExportV2:" . $key, $url, "", 3600 * 24 * 20);
        }
        //删除fileName
        return [
            "url" => $url
        ];
    }

    public function newOrderQueryExport(array $where,int $uid,array $queryData)
    {
        /**
         * 生成导出订单号
         */
        $uiqid = date("YmdHi", time()).$uid;

        if (empty($where['start_date']) || empty($where['end_date']))
            throw new \Exception("服务异常");
        if ($where['end_date'] >= date("Y-m-d"))
            throw new \Exception("服务异常");
        /**
         * 防止前端重复提交
         */
        $redis = Redis::connection('default');

        if (empty($redis))
            throw new \Exception("服务异常");

        if ($redis->exists('system_order_query_export:'.$uiqid))
            throw new \Exception("请稍后再试！");
        $redis->setex("system_order_query_export:" . $uiqid, 30, $uid);

        /**
         * 保存导出信息
         */
        $data = [
            'uid'=>$uid,
            'export_number'=>date("YmdHis", time()).$uid,
            'export_query'=>json_encode($queryData, JSON_UNESCAPED_UNICODE),
            'export_channel'=>2,
        ];
        $id = OrderExportRepo::getInstance()->saveData($data);
        if (!$id)
            throw new \Exception("导出异常");

        /**
         * 指定异步任务处理
         */
        MerchantOrderQueryExportJob::dispatch($where,$id);

    }
    public function excelDownLoad(array $where,$uid)
    {

        Log::info('导出执行开始时间'.date("Y-m-d H:i:s", time()));
        ini_set('memory_limit', '600M');
        set_time_limit(0);
        try {
            $company = StoreAllPayWayList::query()->get()->toArray();
            $company = array_combine(array_column($company, "company"), $company);

            $title = [
                '门店ID', '门店名称', "设备ID", '收银员', '订单号', "原订单号", '订单金额', '退款金额', "实收金额", "实收净额", '费率', '手续费', '支付方式', '支付状态', '付款时间', '通道',
                '退款时间', "退款类型", '备注', "买家ID", "优惠券类型", "优惠券金额"
            ];
            $fileName = "Merchant订单导出" . ($where["month"] ?? date("Y-m-d")) . "_" . date('YmdHis') . ".csv";
            $filePath = public_path("/uploads/csv/" . date("Y/md", time()) . "/");

            /**
             *防止查询条件重复
             */
            if (!empty($where["store_id"])) {
                $storeData[] = $where["store_id"];
            }else{
                $storeData = $where['merchant_all_store_ids'];
            }
            unset($where["store_id"]);
            unset($where["merchant_all_store_ids"]);
            Log::info('服务商导出订单门店ID：',$storeData);

            /**
             * 从新定义查询条件
             */
            foreach ($storeData as $keys => &$value) {
                $flagTitle = empty($keys) ? 1 : 0;
                Log::info('查询门店ID:'.$value);
                $where['store_id'] = $value;
                if (empty($where["start_date"]) || $where["start_date"] >= date("Y-m-d", time()) . " 00:00:00") {
                    $key = "";
                    $prefix = "";
                    $query = DB::connection();
                    $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                    $query = $query->table("orders as " . self::TmpTableName);
                } else {
                    if (!Schema::connection("bi")->hasTable($this->getTableName($where["start_date"], false))) {
                        throw new \Exception("暂无数据");
                    }
                    $prefix = "bi_";
                    $query = DB::connection("bi");
                    $query->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                    $query = $query->table(DB::raw($this->getTableName($where["start_date"], true, $prefix) ));
                }
                $this->setWhere($query, $where, self::TmpTableName);

                $query->select([
                    self::TmpTableName . ".trade_no",
                    self::TmpTableName.'.store_id',
                    self::TmpTableName.'.out_trade_no',
                    self::TmpTableName.'.created_at',
                    self::TmpTableName.'.store_name',
                    self::TmpTableName.'.device_id',
                    self::TmpTableName.'.merchant_name',
                    self::TmpTableName.'.total_amount',
                    self::TmpTableName.'.refund_amount',
                    self::TmpTableName.'.rate',
                    self::TmpTableName.'.fee_amount',
                    self::TmpTableName.'.ways_type_desc',
                    self::TmpTableName.'.pay_status_desc',
                    self::TmpTableName.'.pay_time',
                    self::TmpTableName.'.company',
                    self::TmpTableName.'.remark',
                    self::TmpTableName.'.buyer_id',
                    self::TmpTableName.'.pay_status',
                    self::TmpTableName.'.discount_amount',
                    "refund_orders.created_at as refund_time",
                    "order_goods_details.coupon_amount as goods_coupon_amount",
                    "order_goods_details.coupon_type as goods_coupon_type",
                    "bilog.bi_fee_amount"
                ]);
                $query->leftJoin("refund_orders", self::TmpTableName . ".out_trade_no", "=", "refund_orders.out_trade_no");


                $goodsQuery = DB::table($prefix . "order_goods_details")->select([
                    "out_trade_no", "coupon_type",
                    DB::raw("sum(coupon_amount) as coupon_amount"),
                ])
                    ->where("status", 1)
                    ->groupBy(["out_trade_no", "coupon_type"]);
                $this->setWhere($goodsQuery, [
                    "store_id" => $value,
                    "start_date" => $where["start_date"],
                    "end_date" => $where["end_date"]
                ]);

                $query->leftJoinSub($goodsQuery, $prefix . "order_goods_details", self::TmpTableName . ".out_trade_no", "=", "order_goods_details.out_trade_no");
                if (isset($where["sort"]) && $where["sort"] == 1) {
                    $query->orderBy(self::TmpTableName . ".total_amount", "desc");
                } else if (isset($where["sort"]) && $where["sort"] == 2) {
                    $query->orderBy(self::TmpTableName . ".total_amount", "asc");
                }

                /**
                 * 获取当前副表记录
                 */
                $biLog = DB::connection("bi");
                $biLog->getPdo()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
                $table = $biLog->table('bill_logs_'.date('Ym',strtotime($where['start_date'])));

                $biLogData = $table->select([
                    "out_trade_no",
                    DB::raw("sum(fee_amount) as bi_fee_amount")
                ])
                    ->where("store_id",$value)
                    ->groupBy("out_trade_no");
                $query->leftJoinSub($biLogData, $prefix.'bilog', self::TmpTableName . ".out_trade_no", "=", "bilog.out_trade_no");
                if (!$query->count())
                    continue;

                $orders = $query->orderBy(self::TmpTableName . ".created_at", "desc")->cursor();
                Log::info('导出执行主表查询结束时间'.date("Y-m-d H:i:s", time()));
                $this->generatorExport($filePath, $fileName, $title, $flagTitle,$orders, function ($order) use ($company) {
                    $refund_type_name = "";
                    if ($order->pay_status == 6) {
                        $refund_type_name = "当日退";
                        if (isset($order->refund_time) && isset($order->refund_time)) {
                            if (date("Ymd", strtotime((string)$order->created_at)) != date("Ymd", strtotime((string)$order->refund_time))) {
                                $refund_type_name = "隔日退";
                            }
                            if (date("Ym", strtotime((string)$order->created_at)) != date("Ym", strtotime((string)$order->refund_time))) {
                                $refund_type_name = "隔月退";
                            }
                        }
                    }

                    $coupon_type = 0;
                    $coupon_type_text = "无";
                    $coupon_amount = 0;

                    if (!empty($order->goods_coupon_type) && $order->pay_status == 1) {
                        $coupon_type = $order->goods_coupon_type == "COUPON" ? 1 : 2;
                        $coupon_type_text = $order->goods_coupon_type == "COUPON" ? "预充" : "免充";
                        $coupon_amount = $order->goods_coupon_amount ?? 0;
                    }
                    if ($coupon_type == 0 && $order->discount_amount > 0 && $order->pay_status == 1) {
                        $coupon_type = 1;
                        $coupon_type_text = "预充";
                        $coupon_amount = $order->discount_amount;
                    }

                    $fee_amount = $order->fee_amount;
                    if (!empty($order->bi_fee_amount)) {
                        $fee_amount = $order->bi_fee_amount;
                    }

                    $actual_amount = bcsub((string)$order->total_amount, (string)$order->refund_amount, 2);
                    $receipt_amount = bcsub((string)$actual_amount, (string)$fee_amount, 2);
                    return [
                        iconv('utf-8','gbk',$order->store_id."\t"),
                        $order->store_name,
                        iconv('utf-8','gbk',$order->device_id."\t"),
                        $order->merchant_name,
                        "'" . $order->out_trade_no,
                        iconv('utf-8','gbk',$order->trade_no."\t"),
                        $order->total_amount,
                        $order->refund_amount,
                        $actual_amount,
                        $receipt_amount,
                        $order->rate . "%",
                        $order->fee_amount,
                        $order->ways_type_desc,
                        $order->pay_status_desc,
                        $order->pay_time,
                        isset($company[$order->company]) ? $company[$order->company]["company_desc"] : "",
                        $order->refund_time,
                        $refund_type_name,
                        $order->remark,
                        $order->buyer_id,
                        $coupon_type_text,
                        $coupon_amount,
                    ];
                });
                unset($query);
            }

            /**
             * 防止未有数据时系统异常
             */
            if (file_exists($filePath.$fileName))
            {
                $fileSize = filesize($filePath.$fileName);
                Log::info('订单导出文件大小'.$fileSize);

                /**
                 * 对csv进行压缩
                 */
                $zipArc = new ZipArchive();
                $zipName = $filePath.explode('.',$fileName)[0].'.zip';
                if ($zipArc->open($zipName, ZipArchive::CREATE) === false) {
                    Log::info('订单导出文件打开csv失败');
                    return;
                }
                $zipFilaPath = $filePath.$fileName;
                // 加密文件 此处文件名及路径是压缩包内的
                $zipFile = '导出数据/'.$fileName;
                $zipArc->addFile($zipFilaPath,$zipFile);
                $zipArc->close();
                Log::info("[商户订单导出文件压缩关闭]");

                $url = secure_url("/uploads/csv/" . date("Y/md", time()) . "/".explode('.',$fileName)[0].'.zip');
            }

        }catch (\Exception $e){
            Log::error('商户订单导出异常'.$e->getMessage());
            $url = '';
        }

        /**
         * 删除excel文件
         */
        if (file_exists($filePath . $fileName)) {
            unlink($filePath . $fileName);
        }

        Log::info('导出执行结束时间'.date("Y-m-d H:i:s", time()));
        return ['url'=>$url];
    }
    protected function generatorExport(string $filePath, string $fileName, $title,$flagTitle, \Generator $orders, callable $callable): void
    {
        if (!is_dir($filePath)) {
            mkdir($filePath, 0777, true);
        }
        $file = $filePath . $fileName;
        $fileObj = fopen($file, 'a+');
        fwrite($fileObj, "\xEF\xBB\xBF"); // UTF-8 BOM
        if ($flagTitle)
        {
            fputcsv($fileObj, $title);
        }


        $counter = 0;
        foreach ($orders as $order) {
            fputcsv($fileObj, $callable($order));
            // 每10000行刷新一次文件缓冲
            if (++$counter % 1000 === 0) {
                fflush($fileObj);
                flush();
                gc_collect_cycles();
                // 可选：记录进度
                Log::info("Processed {$counter} records");
            }
            unset($order);
        }
        fclose($fileObj);
    }

}